﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using Entities;
using System.Data.SqlClient;
using DAOExceptions;

namespace SQLDao
{
    public class SQL_HostedFile_DAO : IHostedFileDAO
    {
        public int GetFileID(string short_link)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@shortlink", short_link);
            command.CommandText = "SELECT [id] FROM [HostedFiles] WHERE [short_link]=@shortlink";
            try
            {
                var res = command.ExecuteScalar().ToString();
                int id = -1;
                int.TryParse(res, out id);
                return id;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while checking hosted file existence");
            }
        }

        public HostedFile LoadHostedFile(int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", id);
            command.CommandText = "SELECT [original_name], [serverside_name], [short_link], [file_size], [shared] FROM [HostedFiles] WHERE [id]=@id";
            HostedFile hfile;

            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        // Rly? c# float <-> sql decimal
                        hfile = new HostedFile(id, 0, (string)reader["original_name"], (string)reader["serverside_name"], (string)reader["short_link"], (long)reader["file_size"], ((int)reader["shared"] == 1) ? true : false);
                    }
                    else
                    {
                        return null;
                    }
                }


                command.CommandText = "SELECT [owner_id] FROM [HostedFileOwnership] WHERE [hostedfile_id]=@id";
                int owner = -1;
                var res = command.ExecuteScalar().ToString();
                int.TryParse(res, out owner);
                hfile.OwnerID = owner;
                return hfile;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading hosted file");
            }
        }

        public bool HostedFileExists(int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", id);
            command.CommandText = "SELECT [id] FROM [HostedFiles] WHERE [id]=@id";
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    return reader.HasRows;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while checking hosted file existence");
            }
        }

        public void UnlinkHostedFile(int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", id);
            command.CommandText = "DELETE FROM [HostedFilesOwnership] WHERE [id]=@id";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while unlinking hosted file");
            }
        }

        public void LinkHostedFile(int file_id, int user_id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@hostedfile_id", file_id);
            command.Parameters.AddWithValue("@owner_id", user_id);
            command.CommandText = "INSERT INTO [HostedFilesOwnership] ([hostedfile_id], [owner_id]) VALUES(@hostedfile_id, @owner_id)";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while linking hosted file");
            }
        }

        public int AddHostedFile(HostedFile src)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@original", src.OriginalFileName);
            command.Parameters.AddWithValue("@serverside", src.ServerSideFileName);
            command.Parameters.AddWithValue("@shortlink", src.ShotLinkID);
            command.Parameters.AddWithValue("@filesize", src.FileSize);
            command.Parameters.AddWithValue("@shared", src.Shared ? 1 : 0);

            command.CommandText = "INSERT INTO [HostedFiles]" +
                "([original_name], [serverside_name], [short_link], [file_size], [shared])" +
                "VALUES(@original, @serverside, @shortlink, @filesize, @shared)";

            int id = -1;
            try
            {
                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.CommandText = "SELECT SCOPE_IDENTITY()";
                var res = command.ExecuteScalar().ToString();
                int.TryParse(res, out id);
                if (id == -1)
                    return id;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while adding hosted file");
            }
            LinkHostedFile(id, src.OwnerID);
            return id;
        }

        public void ModifyHostedFile(HostedFile src)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", src.ID);
            command.Parameters.AddWithValue("@original", src.OriginalFileName);
            command.Parameters.AddWithValue("@serverside", src.ServerSideFileName);
            command.Parameters.AddWithValue("@shortlink", src.ShotLinkID);
            command.Parameters.AddWithValue("@filesize", src.FileSize);
            command.Parameters.AddWithValue("@shared", src.Shared ? 1 : 0);

            command.CommandText = "UPDATE [HostedFiles]" +
                "SET [original_name]=@original, [serverside_name]=@serverside, [short_link]=@shortlink, [file_size]=@filesize, [shared]=@shared WHERE [id]=@id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while modifying hosted file");
            }
            UnlinkHostedFile(src.ID);
            LinkHostedFile(src.ID, src.OwnerID);
        }

        public void RemoveHostedFile(HostedFile src)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@id", src.ID);
            UnlinkHostedFile(src.ID);
            command.CommandText = "DELETE FROM [HostedFiles] WHERE [id]=@id";
            try{
            command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while removing hosted file");
            }
        }

        public void RemoveHostedFile(int src)
        {
            RemoveHostedFile(new HostedFile(src));
        }

        public List<int> GetHostedFilesForUser(int user_id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.AddWithValue("@ownerid", user_id);
            command.CommandText = "SELECT [id] FROM [HostedFilesOwnership] WHERE [owner_id]=@ownerid";

            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    var list = new List<int>();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            list.Add((int)reader["id"]);
                        }
                    }
                    return list;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading hosted files list");
            }
        }
    }
}
